/*
  DOTSS 0.1 database configuration script
*/

SET NOCOUNT ON

IF NOT EXISTS(SELECT 'x' FROM sys.schemas WHERE name='DOTSS')
	EXEC ('CREATE SCHEMA DOTSS');

-- Capture the schema id so that we can further check for objects
DECLARE @SchemaID	INT
SELECT @SchemaID = schema_id FROM sys.schemas WHERE name='DOTSS'

IF NOT EXISTS (SELECT 'x' FROM sys.tables WHERE name='SchemaVersion' AND schema_id=@SchemaID)
	CREATE TABLE DOTSS.SchemaVersion (
		Version		VARCHAR(20)		PRIMARY KEY,
		CreateDate	SMALLDATETIME		DEFAULT(GetDate()),
		Description	VARCHAR(1000)
	);

IF NOT EXISTS (SELECT 'x' FROM sys.tables WHERE name='Session' AND schema_id=@SchemaID)
	CREATE TABLE DOTSS.Session (
		ID		VARCHAR(30)	NOT NULL,
		ApplicationName	VARCHAR(50)	NOT NULL,
		EnvName		VARCHAR(50)	NOT NULL,
		CreateDate	DATETIME	NOT NULL	DEFAULT(GetDate()),
		ExpireDate	DATETIME	NOT NULL,
		LockDate	DATETIME	NOT NULL,
		LockID		SMALLINT	NOT NULL	DEFAULT(0),
		Locked		TINYINT		NOT NULL	DEFAULT(1),
		Items		VARBINARY(MAX),
		PRIMARY KEY(ID, ApplicationName, EnvName)
	);

IF NOT EXISTS (SELECT 'x' FROM sys.indexes WHERE name='IX_Session_Expire')
CREATE INDEX IX_Session_Expire ON DOTSS.Session(ExpireDate);

IF EXISTS (SELECT 'x' FROM sys.procedures WHERE name='CreateNewItem' AND schema_id=@SchemaID)
DROP PROC DOTSS.CreateNewItem;

EXEC ('
CREATE PROC DOTSS.CreateNewItem
	@SessionID	VARCHAR(30),
	@TimeoutMinutes	INT,
	@AppName	VARCHAR(50),
	@EnvName	VARCHAR(50)
AS
BEGIN

	INSERT INTO DOTSS.Session (
		ID,
		ApplicationName,
		EnvName,
		ExpireDate,
		LockDate
	) SELECT
		@SessionID,
		@AppName,
		@EnvName,
		DATEADD(minute, @TimeoutMinutes, GetDate()),
		GetDate()

END')


IF EXISTS (SELECT 'x' FROM sys.procedures WHERE schema_id = @SchemaID AND Name='GetItem')
DROP PROC DOTSS.GetItem


EXEC ('CREATE PROC DOTSS.GetItem
	@SessionID		VARCHAR(30),
	@ApplicationName	VARCHAR(50),
	@EnvName		VARCHAR(50),
	@Lock			TINYINT		= 0,
	@Locked			TINYINT OUTPUT
AS
BEGIN

	DECLARE @Now		DATETIME		SET @Now = GetDate()

	-- A lock has been requested on this session, attempt to get the lock
	IF @Lock > 0
	BEGIN

		UPDATE DOTSS.Session
		SET
			Locked		= 1,
			LockDate	= @Now
		WHERE
			ID		= @SessionID
		AND	ApplicationName	= @ApplicationName
		AND	EnvName		= @EnvName
		AND	Locked		= 0

		SET @Locked		= @@ROWCOUNT

	END

	-- If we got the lock, update the lock id immediately
	IF @Locked > 0
	UPDATE
		DOTSS.Session
	SET
		LockID			= LockID + 1
	WHERE
		ID			= @SessionID
	AND	ApplicationName		= @ApplicationName
	AND	EnvName			= @EnvName

	-- Return the data
	SELECT 
		*
	FROM
		DOTSS.Session (nolock)
	WHERE
		ID		= @SessionID
	AND	ApplicationName	= @ApplicationName
	AND	EnvName		= @EnvName

END')

IF EXISTS (SELECT 'x' FROM sys.procedures WHERE name='ReleaseLock' AND schema_id=@SchemaID)
DROP PROC DOTSS.ReleaseLock

EXEC ('CREATE PROC DOTSS.ReleaseLock
	@SessionID		VARCHAR(30),
	@ApplicationName	VARCHAR(50),
	@EnvName		VARCHAR(50),
	@LockID			SMALLINT,
	@TimeoutMinutes		SMALLINT
AS
BEGIN

	UPDATE
		Session
	SET
		Locked		= 0,
		ExpireDate	= DATEADD(minute, @TimeoutMinutes, GetDate())
	WHERE
		ID		= @SessionID
	AND	ApplicationName	= @ApplicationName
	AND	EnvName		= @EnvName
	AND	LockID		= @LockID

END
')

IF EXISTS (SELECT 'x' FROM sys.procedures WHERE name='RemoveItem' AND schema_id=@SchemaID)
DROP PROC DOTSS.RemoveItem 

EXEC ('CREATE PROC DOTSS.RemoveItem
	@SessionID		VARCHAR(30),
	@ApplicationName	VARCHAR(50),
	@EnvName		VARCHAR(50),
	@LockID			SMALLINT
AS
BEGIN

	DELETE FROM DOTSS.Session
	WHERE
		ID		= @SessionID
	AND	ApplicationName	= @ApplicationName
	AND	EnvName		= @EnvName
	AND	LockID		= @LockID

END')

IF EXISTS (SELECT 'x' FROM sys.procedures WHERE name='ResetTimeout' AND schema_id = @SchemaID)
DROP PROC DOTSS.ResetTimeout

EXEC ('CREATE PROC DOTSS.ResetTimeout
	@SessionID		VARCHAR(30),
	@ApplicationName	VARCHAR(50),
	@EnvName		VARCHAR(50),
	@TimeoutMinutes		SMALLINT
AS
BEGIN

	UPDATE
		DOTSS.Session
	SET
		ExpireDate	= DATEADD(minute, @TimeoutMinutes, GetDate()) 
	WHERE
		ID		= @SessionID
	AND	ApplicationName	= @ApplicationName
	AND	EnvName		= @EnvName

END')

IF EXISTS (SELECT 'x' FROM sys.procedures WHERE name='SetItem' AND schema_id=@SchemaID)
DROP PROC DOTSS.SetItem

EXEC ('CREATE PROC DOTSS.SetItem
	@SessionID		VARCHAR(30),
	@ApplicationName	VARCHAR(50),
	@EnvName		VARCHAR(50),
	@TimeoutMinutes		SMALLINT,
	@LockID			SMALLINT,
	@Items			VARBINARY(MAX)
AS
BEGIN

	DECLARE @Now		DATETIME		SET @Now	= GetDate()
	DECLARE @ExpireDate	DATETIME		SET @ExpireDate = DATEADD(minute, @TimeoutMinutes, @Now)

	-- Cleanup any expired Session information
	DELETE FROM Session WHERE ExpireDate < @Now

	IF NOT EXISTS (SELECT ''x'' FROM Session WHERE ID = @SessionID AND ApplicationName=@ApplicationName AND EnvName= @EnvName)
		INSERT INTO Session (
			ID,
			ApplicationName,
			EnvName,
			CreateDate,
			ExpireDate,
			LockDate,
			Items
		) SELECT
			@SessionID,
			@ApplicationName,
			@EnvName,
			@Now,
			@ExpireDate,
			@Now,
			@Items
	ELSE
		UPDATE
			Session
		SET
			ExpireDate	= @ExpireDate,
			Items		= @Items,
			Locked		= 0
		WHERE
			ID		= @SessionID
		AND	ApplicationName = @ApplicationName
		AND	EnvName		= @EnvName
		AND	LockID		= @LockID

END')

IF NOT EXISTS (SELECT 'x' FROM DOTSS.SchemaVersion WHERE Version='0.1.0.0')
	INSERT INTO DOTSS.SchemaVersion (Version, Description)
	VALUES ('0.1.0.0', 'Initial schema');
ELSE
	UPDATE DOTSS.SchemaVersion SET CreateDate=GetDate() WHERE Version='0.1.0.0'

SELECT * FROM DOTSS.SchemaVersion

SET NOCOUNT OFF